package com.zm.test.devView;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

public class GraphTableExcelFactory implements GraphTableFactory {

	private final static DecimalFormat NumericFormat = new DecimalFormat("#.#");

	private String excelFile = "";

	public GraphTableExcelFactory(String excelFile) {
		this.excelFile = excelFile;
	}

	/**
	 * @throws Exception
	 * 
	 */
	private GraphTable getGraph(HSSFSheet sheet) throws Exception {

		GraphTable graph = new GraphTable();

		// set table name
		String pdcName = sheet.getSheetName();
		graph.setPdcName(pdcName);

		// 第一行,第一列, read (topx,topy,width,height)
		String topCoord = getCellStringValue(sheet, 0, 0);
		String[] _topCoords = topCoord.split(",");
		if (_topCoords.length != 4) {
			throw new Exception("shell " + pdcName + " cell(0,0) format error,[" + topCoord
					+ "], need ex:550,270,100,20");
		}
		int topx = Integer.parseInt(_topCoords[0]);
		int topy = Integer.parseInt(_topCoords[1]);
		int width = Integer.parseInt(_topCoords[2]);
		int height = Integer.parseInt(_topCoords[3]);

		graph.setCellWidth(width);
		graph.setCellHeight(height);

		// 第一行，标题
		// get field name info, row=1
		List<String> fieldNameList = new ArrayList<String>();
		// x coord
		List<Integer> rowCoordxList = new ArrayList<Integer>();
		//
		int lastx = topx;
		//
		for (int celli = 1; StringUtils.isNotBlank(getCellStringValue(sheet, 0, celli)); celli++) {
			String _str = getCellStringValue(sheet, 0, celli);
			//
			fieldNameList.add(_str);

			// 计算x坐标
			if (StringUtils.startsWith(_str, "_space_")) {
				Integer spacex = Integer.parseInt(StringUtils.substring(_str, 7));
				lastx += spacex;
				// _space_不计坐标
			} else {
				rowCoordxList.add(lastx);
				lastx += width;
			}
		}
		graph.setLabels(fieldNameList);
		graph.setCoordxs(rowCoordxList);

		// field count
		int fieldCount = fieldNameList.size();

		// ---------------
		// get field value table, 从第二行，第二列开始取内容
		List<List<String>> attrList = new ArrayList<List<String>>();

		// y coord
		List<Integer> rowCoordyList = new ArrayList<Integer>();
		//
		int lasty = topy;
		//
		for (int ri = 1; StringUtils.isNotBlank(getCellStringValue(sheet, ri, 0)); ri++) {
			List<String> rowAttrList = new ArrayList<String>();

			for (int celli = 1; celli <= fieldCount; celli++) {
				String _v = getCellStringValue(sheet, ri, celli);
				// 空单元格不要
				if (StringUtils.isNotBlank(_v)) {
					rowAttrList.add(_v);
				}

			}

			attrList.add(rowAttrList);
			rowCoordyList.add(lasty);
			// 计算y坐标
			lasty += height;
		}
		graph.setCoordys(rowCoordyList);
		graph.setAttrs(attrList);

		return graph;
	}

	/**
	 * get a table data
	 * 
	 * @throws Exception
	 */
	public GraphTable getGraphTable(String sheetName) throws Exception {
		HSSFWorkbook wb = null;
		FileInputStream fileOut = null;
		try {
			fileOut = new FileInputStream(excelFile);
			wb = new HSSFWorkbook(fileOut);
			HSSFSheet sheet = wb.getSheet(sheetName);
			return this.getGraph(sheet);

		} catch (IOException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				fileOut.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}

	}

	/**
	 * get all data
	 * 
	 * @throws Exception
	 */
	public List<GraphTable> getAllGraphTable() throws Exception {
		List<GraphTable> tableInitDataList = new ArrayList<GraphTable>();
		HSSFWorkbook wb = null;
		FileInputStream fileOut = null;
		try {
			fileOut = new FileInputStream(excelFile);
			wb = new HSSFWorkbook(fileOut);

			for (int i = 0; i < wb.getNumberOfSheets(); i++) {
				tableInitDataList.add(this.getGraph(wb.getSheetAt(i)));
			}

		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				fileOut.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}

		return tableInitDataList;
	}

	/**
	 * 取单元格文本
	 * 
	 * @param sheet
	 * @param rowNum
	 * @param cellNum
	 * @return
	 * @throws Exception
	 */
	private String getCellStringValue(HSSFSheet sheet, int rowNum, int cellNum) throws Exception {
		String rs = "";
		try {
			if (sheet == null) {
				return null;
			}
			HSSFRow row = sheet.getRow(rowNum);
			if (row == null) {
				return null;
			}
			HSSFCell cell = row.getCell(cellNum);
			if (cell == null) {
				return null;
			}
			if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
				return null;
			} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
				rs = cell.getStringCellValue();
			} else {
				rs = NumericFormat.format(cell.getNumericCellValue());
			}
			// rs = cell.getRichStringCellValue().getString();
			// rs = cell.getStringCellValue();
		} catch (Exception e) {
			System.out.println(e.toString() + ": " + sheet.getSheetName() + " " + rowNum + "," + cellNum);
			throw e;
		}
		return rs.length() == 0 ? null : rs;
	}
}